﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SMS_GetOnlinePerson]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SMS_GetOnlinePerson];
GO
CREATE PROCEDURE [dbo].[sproc_SMS_GetOnlinePerson]
/*----------------------------------
功能：得到所有在线人员
-----------------------------------*/
AS
BEGIN
SET NOCOUNT ON

SELECT
	UDS_Staff.*,
    UDS_Position.Position_Name,
    UDS_Online.LoginTime,
    UDS_Online.IpAddr,
    UDS_Online.ActiveTime,
	datediff(ss,UDS_Online.LoginTime,UDS_Online.ActiveTime) AS onlinetime,
    CASE
		WHEN ( SELECT TOP 1 ClassName FROM UDS_CLASS WHERE UDS_CLASS.CLASSID = UDS_Online.ActiveNodeID )  IS NULL THEN N'桌面'
		ELSE ( SELECT TOP 1 ClassName FROM UDS_CLASS WHERE UDS_CLASS.CLASSID=UDS_Online.ActiveNodeID )  
    END AS ActiveClass
FROM
    UDS_Online,
    UDS_Staff,
    UDS_Staff_In_Position,
    UDS_Position
WHERE
	UDS_Online.Username = UDS_Staff.staff_name
    AND UDS_Staff_In_Position.Staff_ID = UDS_Staff.Staff_ID
    AND UDS_Position.Position_ID = UDS_Staff_In_Position.Position_ID
ORDER BY
	onlinetime

END
GO